Release 10.1A: OpenEdge Data Management:
SQL Reference

UPDATE

Updates the rows and columns of the specified table with the given values for rows that satisfy the search_condition.

Syntax

UPDATE table_name SET assignment [, assignment ] , ... [ WHERE search_condition ] ;

assignment:

This is the syntax for assignment:

Syntax

column = { expr | NULL } | ( column [, column ] , ... ) = ( expr [, expr ] ) | ( column [, column ] , ... ) = ( query_expression )

Notes

If you specify the optional WHERE clause, only rows that satisfy the search_condition are updated. If you do not specify a WHERE clause, all rows of the table are updated.
If the expressions in the SET clause are dependent on the columns of the target table, the expressions are evaluated for each row of the table.
If a query expression is specified on the right-hand side of an assignment, the number of expressions in the first SELECT clause of the query expression must be the same as the number of columns listed on the left-hand side of the assignment.
If a query expression is specified on the right-hand side of an assignment, the query expression must return one row.
If a table has check constraints and if the columns to be updated are part of a check expression, then the check expression is evaluated. If the result of the evaluation is FALSE, the UPDATE statement fails.
If a table has primary or candidate keys and if the columns to be updated are part of the primary or candidate key, SQL checks to determine if there is a corresponding row in the referencing table. If there is a corresponding row the UPDATE operation fails.
Column names in the SET clause do not need a table_name qualifier. Since an UPDATE statement affects a single table, columns in the SET clause are implicitly qualified to the table_name identified in the UPDATE clause.

Examples

UPDATE Orderline SET Qty = 186 Where Ordernum = 22; Update Orderline SET (Itemnum) = (Select Itemnum FROM Item WHERE Itemname = 'Tennis balls') WHERE Ordernum = 20; UPDATE Orderline SET (Qty) = (200 * 30) WHERE OrderNum = 19; UPDATE OrderLine SET (ItemNum, Price) = (SELECT ItemNum, Price * 3 FROM Item WHERE ItemName = 'gloves') WHERE OrderNum = 21 ;

Authorization

Must have DBA privilege or UPDATE privileges on all the specified columns of the target table, and SELECT privilege on all the other tables referred to in the statement.

Related statements

SELECT, OPEN, FETCH